/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import requeteur.Paragraphe;
import requeteur.Resultat;
import requeteur.ResultatCollection;
import requeteur.TermeReq;

/**
 *
 * @author Nargisse
 */
public class DBWriter {
    private TermeReq terme;
    public static Connection connectToBDD;
    public static Statement st;
    public static  Statement myStatement;
    private StringBuilder requestBuffer;
    private StringBuilder requestBufferDoc;
    private StringBuilder requestBufferNoeud;
    private StringBuilder requestBufferPath;
    public static ResultSet result;
    private String idNoeud;
    private int nbReqTermes;
    private static final int packetLimit = 1040000;
    private ResultatCollection rc;
    
    public DBWriter() {
        nbReqTermes = 0;
        try {
            requestBuffer = new StringBuilder("INSERT INTO termes(terme,position) VALUES ");
            connectToBDD();
        } catch (Exception ex) {
            Logger.getLogger(DBWriter.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /**********************************************
** Fonction qui permet de se connecter       **
**              a la BDD                     **
**********************************************/        
public void connectToBDD() throws Exception{
    
    System.out.println("Connection BDD ");
    String url = "jdbc:mysql://localhost:3306/RI_Index";
    
    Class.forName("com.mysql.jdbc.Driver");
    connectToBDD = DriverManager.getConnection (url, "root",  ""); 
    if(connectToBDD == null){
        System.out.println("PB avec la connection a la BDD! ");
    } else {
        myStatement = connectToBDD.createStatement();
    }
    
   //Statement object is instantiated from connection object by calling createStatement() method. 
        //Statement object is to send and execute sql statements to database.
        /*  Statement has three method
        executeQuery() is used for SELECT statement.
        executeUpdate() is used for create, alter or drop table.
        execute() is used when sql statement written as string object.*/
} 

/**********************************************
** Fonction qui permet de se deconnecter     **
**              a la BDD                     **
**********************************************/  
 public void deconnectToBDD() throws SQLException{
     System.out.println("Deconnection BDD ");
     
     if(myStatement != null){
         myStatement.close();
     }
     
     if(connectToBDD != null){
         connectToBDD.close();
     }  
 }
 
 public void addTerme(String terme, boolean exec) {
        try {
            requestBuffer.append("('"+terme+"','0')");
            nbReqTermes++;
            if (exec || nbReqTermes>10000) {
                //System.out.println("Execution");
                requestBuffer.append(';');
                myStatement.executeUpdate(requestBuffer.toString());
                requestBuffer=new StringBuilder("INSERT INTO termes(terme,position) VALUES ");
                nbReqTermes = 0;
                //TODO si nécessaire : reset le requestBuffer
            } else {
                requestBuffer.append(',');
            }
        } catch (SQLException ex) {
            System.out.println("Erreur en insérant un terme : "+terme);
            Logger.getLogger(DBWriter.class.getName()).log(Level.SEVERE, null, ex);
           System.exit(777);
        }
 }

    void updateTermes(String values) {
        try {
            myStatement.executeUpdate("INSERT INTO termes(terme,position) VALUES "
                    +values);
        } catch (SQLException ex) {
            System.out.println("Erreur en exécutant les insertions de termes");
            Logger.getLogger(DBWriter.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
     
      void updateTermes(ArrayList<String> values) {
        for (String val : values) {
            try {
            myStatement.executeUpdate("INSERT INTO termes(terme,position) VALUES "
                    +val);
            } catch (SQLException ex) {
                System.out.println("Erreur en exécutant l'insertion de : "+ val);
                Logger.getLogger(DBWriter.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
    } 
    
    private void updateRequestWithPacketLimit(String reqPattern, ArrayList<String> values) {
        StringBuilder req = new StringBuilder(reqPattern);
        for (String val : values) {
            if (req.length() + val.length() > DBWriter.packetLimit) {
                try {
                    req.deleteCharAt(req.length()-1); //on enlève la virgule en trop
                    myStatement.executeUpdate(req.toString());
                } catch (SQLException ex) {
                    System.out.println("Erreur en exécutant l'insertion de : "+ req);
                    Logger.getLogger(DBWriter.class.getName()).log(Level.SEVERE, null, ex);
                }
                req.setLength(0); // on réinitialise le buffer
                req.append(reqPattern);
            }
            req.append(val).append(',');
        }
        try {
            req.deleteCharAt(req.length()-1); //on enlève la virgule en trop
            myStatement.executeUpdate(req.toString());
        } catch (SQLException ex) {
            System.out.println("Erreur en exécutant l'insertion de : "+ req);
            Logger.getLogger(DBWriter.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
  
    public void updateTermesWithPacketLimit(ArrayList<String> values){
        this.updateRequestWithPacketLimit("INSERT INTO termes(terme,position) VALUES ", values);
    }
    
        
    public void updateDocumentsWithPacketLimit(ArrayList<String> values){
        this.updateRequestWithPacketLimit("INSERT INTO document(id_doc,nom) VALUES ", values);
    }
    
    public void updateNoeudsWithPacketLimit(ArrayList<String> values){
        this.updateRequestWithPacketLimit("INSERT INTO noeud(id_noeud,longueur,poids,nom,id_doc,xpath) VALUES ", values);
    }
    
    public String getIdNoeud(){
        return this.idNoeud;
    }
 
    public void setIdNoeud(String id_Noeud){
        this.idNoeud = id_Noeud;
    }
  
    public TermeReq getTermeReqFromBDD(String mot){
        //SELECT * FROM `termes` WHERE terme = "toto";
        String terme = null;
        String position = null;
        try {
            result = myStatement.executeQuery("SELECT * FROM `termes` WHERE `terme` = '"+mot+"'");
            if(result.next()){
                terme = result.getString("terme"); 
                position = result.getString("position"); 
                //System.out.println("terme - "+terme+" | pos - "+position);
                TermeReq t = new TermeReq(terme,position);  
                result.close();
                return t;
            }else{
                System.out.println("Le mot de la requete n'existe pas dans la BDD");
                return null;
            }    
        } catch (SQLException ex) {
            System.out.println("Erreur en recuperant le terme ds la BDD ");
            Logger.getLogger(DBWriter.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }  
    }
    
    private static HashMap<Integer,Double> parsePositions(String positions) {
        
        HashMap<Integer,Double> res = new HashMap<Integer,Double>();
        String[] posTab;
        posTab = positions.split(";");
       // System.out.println("-1- terme => "+t[0]);
        for(int i=0; i<posTab.length; i++){
            String[] idEtPoids = posTab[i].split(",");
            res.put(Integer.parseInt(idEtPoids[0]),Double.parseDouble(idEtPoids[1]));   
        }  
        return res;
    }
    
    public HashMap<Integer,Double> getPositionsOfTerme(String mot){
        //SELECT * FROM `termes` WHERE terme = "toto";
        try {
            result = myStatement.executeQuery("SELECT * FROM `termes` WHERE `terme` = '"+mot+"'");
            if(result.next()){
                String positions = result.getString("position");   
                result.close();
                return parsePositions(positions); //on retourne le résultat formaté en objet java
            }else{
                System.out.println("Le mot de la requete n'existe pas dans la BDD");
                return new HashMap<Integer,Double>(); //on retourne une hashmap vide
            }    
        } catch (SQLException ex) {
            System.out.println("Erreur en recuperant le terme ds la BDD ");
            Logger.getLogger(DBWriter.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }  
    }
    
    public ArrayList<Resultat> getResultWithListIdPara(ArrayList<Paragraphe> listparagraphe){
        this.rc = new ResultatCollection();
        Resultat res= new Resultat(listparagraphe);
        /*
         * SELECT DOCUMENT.nom,NOEUD.nom,NOEUD.xpath,NOEUD.id_doc FROM  `noeud`, `document`
         * WHERE (id_noeud =6 AND DOCUMENT.id_doc = NOEUD.id_doc) 
         * OR (id_noeud =74 AND DOCUMENT.id_doc = NOEUD.id_doc);
         */
        StringBuilder req = new StringBuilder();
    
        for(Paragraphe p : listparagraphe){
            System.out.println("PARA "+p.getIdParagraphe());
            res.setScore(p.getPoidsParag());
            req.append("(`id_noeud`= "+p.getIdParagraphe()+" AND DOCUMENT.id_doc = NOEUD.id_doc)OR");
        }
        req.deleteCharAt(req.length()-1); //on enleve le R
        req.deleteCharAt(req.length()-1); //on enleve le O
        System.out.println("REQ = "+req);
        try{
            result = myStatement.executeQuery("SELECT DOCUMENT.nom,NOEUD.nom,NOEUD.xpath,NOEUD.id_doc FROM `noeud`, `document` WHERE "+req);
            System.out.println("resultat req "+result.next());
  
            if(result.next()){
                while(result.next()){
                    res.setDocName(result.getString("DOCUMENT.nom"));
                    res.setXPath(result.getString("NOEUD.xpath"));
                    res.setNomBalise(result.getString("NOEUD.nom"));
                    System.out.println("NOMDOC "+res.getDocName()+" PATH "+res.getXPath()
                        +" NOMBALISE "+res.getNomBalise()+" SCRORE "+res.getScrore());
                    this.rc.addResult(res);

                }
            }else{
                System.out.println("Echec en recuperant les vals de resultat lie aux paragraphes");
            }
            
           
            result.close();
            
            return rc.getListResult();
         } catch (SQLException ex) {
            Logger.getLogger(DBWriter.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("Echec en recuperant les vals de resultat lie aux paragraphes");
            return null;
        }
    }

    public void resetDB() {
        try {
            this.myStatement.executeUpdate("DELETE FROM termes");
            this.myStatement.executeUpdate("DELETE FROM document");
            this.myStatement.executeUpdate("DELETE FROM noeud");
        } catch (SQLException ex) {
            Logger.getLogger(DBWriter.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("Echec du reset de la BDD");
        }
    }
    
    public ResultatCollection getListResult(){
        return this.rc;
    }

    public String getXPathFromNode(int id) {
        String xpath = null;
        try {
            result = myStatement.executeQuery("SELECT * FROM `noeud` WHERE `id_noeud` = '"+id+"'");
            if(result.next()){
                xpath = result.getString("xpath");   
                result.close();
                 //on retourne le résultat formaté en objet java
            }else{
                System.out.println("Le mot de la requete n'existe pas dans la BDD");
            }    
        } catch (SQLException ex) {
            System.out.println("Erreur en recuperant le terme ds la BDD ");
            Logger.getLogger(DBWriter.class.getName()).log(Level.SEVERE, null, ex);
        } 
        return xpath;
    }

    public int getDocIdFromNode(int idParagraphe) {
        int docId = -1;
        try {
            result = myStatement.executeQuery("SELECT * FROM `noeud` WHERE `id_noeud` = '"+idParagraphe+"'");
            if(result.next()){
                docId = Integer.parseInt(result.getString("id_doc"));   
                result.close();
                 //on retourne le résultat formaté en objet java
            }else{
                System.out.println("Le mot de la requete n'existe pas dans la BDD");
            }    
        } catch (SQLException ex) {
            System.out.println("Erreur en recuperant le terme ds la BDD ");
            Logger.getLogger(DBWriter.class.getName()).log(Level.SEVERE, null, ex);
        } 
        return docId;
    }
    
    public int getNodeIdFromNodeInfo(String file, String xpath) {
        int nodeId = -1;
        
        try {
            result = myStatement.executeQuery("SELECT * FROM noeud,document WHERE noeud.xpath = '"
                    +xpath+"'"+" AND document.nom = '"+file+"'");
            if(result.next()){
                nodeId = Integer.parseInt(result.getString("id_noeud"));   
                result.close();
                 //on retourne le résultat formaté en objet java
            }else{
                System.out.println("Le mot de la requete n'existe pas dans la BDD");
            }    
        } catch (SQLException ex) {
            System.out.println("Erreur en recuperant le terme ds la BDD ");
            Logger.getLogger(DBWriter.class.getName()).log(Level.SEVERE, null, ex);
        } 
        return nodeId;
    }
}
